﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Odbc;
using MySql.Data;
using MySql.Data.MySqlClient;


namespace Supermarkets
{
    class MySQLManager
    {
        private MySqlConnection Connection;
        private Dictionary<int, string> Products;

        private void Connect()
        {
            string connStr = "server=localhost;user=root;database=Market;port=3306;";
            Connection = new MySqlConnection(connStr);
            try
            {
                Console.WriteLine("Connecting to MySQL...");
                Connection.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            Console.WriteLine("Done.");
        }

        public Dictionary<int, string> LoadProductData() 
        {
            Connect();
            Products = new Dictionary<int, string>();

            string sql = "SELECT ID, Name FROM Products";
            MySqlCommand cmd = new MySqlCommand(sql, Connection);
            MySqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                Products.Add((int)(reader[0]), (string)(reader[1]));
            }

            reader.Close();
            Close();

            return Products;
        }

        private void Close() 
        {
            Connection.Close();
        }
    }
}
